# 学生在线管理系统数据库版
import pymysql

tips = ('1:查询学生信息', '2:增加学生信息', '3:删除学生信息', '4修改学生信息', '5：退出系统')


class Stu:
    db = None

    def __init__(self=None, name=None, age=None, sex=None, className=None):
        self.name = name
        self.age = age
        self.sex = sex
        self.className = className
        self.db = self.__connectSql()

    def findAll(self, type, query=None):
        cursor = self.db.cursor()
        sql = 'select * from stu '
        if type == 1:
            sql += 'where id =%d' % (query)
        elif type == 2:
            sql += 'where name like"%%%%%s%%%%"' % (query)
        count = cursor.execute(sql)
        data = cursor.fetchall()
        if count > 0:
        	print('')
        	print('查询到的学生信息如下:')
        	print("%s %2s %s %2s %5s" % ('学号', '姓名', '年龄', '性别', '班级'))
        	for k in data:
        		for i in k:
        			print(i, end="  ")
        		print('')
        else:
            print('未查到学生信息！')

    def insertStu(self):
        cursor = self.db.cursor()
        insertData = (self.name, self.age, self.sex, self.className)
        sql = 'insert into stu (name,age,sex,className) values (\'%s\',%d,\'%s\',\'%s\')' % (
            insertData)
        count = 500
        try:
            count = cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            print('新增学生信息失败。。')
            print('error:=====', e)
            count = -999
            self.db.rollback()
        finally:
            if(count == 1):
                print('=' * 30)
                print('')
                print('新增学生信息成功!!')
                self.findAll(3)
            else:
                print('没查到学生信息')
            print("=" * 30)

    def delStu(self, id):
        cursor = self.db.cursor()
        count = 500
        try:
            sql = 'delete from stu where id =%d' % (id)
            count = cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            print('删除学生信息失败。。')
            print('error:=====', e)
            count = -999
            self.db.rollback()
        finally:
            if(count == 1):
                print('=' * 30)
                print('')
                print('删除学生信息成功!!')
                self.findAll(3)
            else:
                print('没有该学生记录！')
            print("=" * 30)

    def updateStu(self, sid):
        cursor = self.db.cursor()
        insertData = (self.name, self.age, self.sex, self.className, sid)
        sql = 'update stu set name=\'%s\' ,age = %d,sex =\'%s\',className= \'%s\' where id=%d' % (
            insertData)
        count = 500
        try:
            count = cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            print('修改学生信息失败。。')
            print('error:=====', e)
            count = -999
            self.db.rollback()
        finally:
            if(count == 1):
                print('=' * 30)
                print('')
                print('修改学生信息成功!，学生信息列表如下：')
                self.findAll(3)
            else:
                print('没查到学生信息')
            print("=" * 30)

    def __connectSql(self):
        db = pymysql.connect(host='localhost', user='root',
                             password='root', db='student', charset='utf8')
        return db

    def __del__(self):
        self.db.close()


# 学院信息管理，tips:需要展示的功能菜单
# tips 展示功能列表
def showInfo(tips=tips):
    print('')
    print('=' * 20 + '学生信息管理' + '=' * 20)
    for k in range(len(tips)):
        if (k + 1) % 2 == 0:
           # 偶数个折行
            print('{:>4}{}'.format("", tips[k]))
        else:
            print('%s' % tips[k], end="")
    print('')
    print('=' * 52)
    print('')


def main():
    while True:
        showInfo()
        choice = int(input('请输入对应操作序号：'))
        print('')
        if choice == 1:
            stu = Stu()
            print('1:根据id查询 \t 2:根据姓名查询 \t 3：查询全部')
            print('')
            type = int(input('请输入查询类型序号：'))
            query = None
            if type == 1:
                query = int(input('请输入查询学生学号：'))
            elif type == 2:
                query = str(input('请输入学生名称，可模糊匹配：'))
            stu.findAll(type, query)

            print('')
        elif choice == 2:
            print('=' * 20, '添加学生信息', '=' * 20)
            name = input('请输入学生名称：')
            age = int(input('请输入学生年龄：'))
            sex = input('请输入学生性别(man or women):')
            className = input('请输入学生班级名称：')
            stu = Stu(name, age, sex, className)
            stu.insertStu()
        elif choice == 3:
            stu = Stu()
            stu.findAll(3)
            print('')
            sid = int(input('请输入需要删除的学生id:'))
            stu.delStu(sid)
        elif choice == 4:
            print('=' * 20, '修改学生信息', '=' * 20)
            stu = Stu()
            stu.findAll(3)
            print('')
            sid = int(input('请输入需要修改的学生id:'))
            name = input('请输入更改后的学生名称：')
            age = int(input('请输入学生年龄：'))
            sex = input('请输入学生性别(man or women):')
            className = input('请输入学生班级名称：')
            stu = Stu(name, age, sex, className)
            stu.updateStu(sid)
        else:
            print('==' * 10 + '再见' + '==' * 10)
            exit()

if __name__ == '__main__':
    main()
